package dbcTools;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.Stack;

import dbcTools.PSSet.PSSet;
import dbcTools.PSSet.PSSetBlob;
import dbcTools.PSSet.PSSetBoolean;
import dbcTools.PSSet.PSSetInt;
import dbcTools.PSSet.PSSetLong;
import dbcTools.PSSet.PSSetString;
import dbcTools.PSSet.PSSetTimeStamp;
import dbcTools.operationSql.Delete;
import dbcTools.operationSql.Exists;
import dbcTools.operationSql.Insert;
import dbcTools.operationSql.NotExists;
import dbcTools.operationSql.OperationSql;
import dbcTools.operationSql.Select;
import dbcTools.operationSql.Update;
import dbcTools.operationSql.Where;


public class DB_Operation {
	
	private Connection connection;//用于存储数据库链接
	
	private ArrayList<PSSet> psSets;//用于存储？中的值应该执行什么样的操作在PrepareStatement中
	private ArrayList<Object> values;//用于存储？中的值，下标与psSets一一对应
	
	private final static PSSetBlob psSetBlob = new PSSetBlob();//专门使用PrepareStatement设置Blob类型的数据
	private final static PSSetBoolean psSetBoolean = new PSSetBoolean();//专门使用PrepareStatement设置Boolean类型的数据
	private final static PSSetInt psSetInt = new PSSetInt();//专门使用PrepareStatement设置Int类型的数据
	private final static PSSetLong psSetLong = new PSSetLong();//专门使用PrepareStatement设置Long类型的数据
	private final static PSSetString psSetString = new PSSetString();//专门使用PrepareStatement设置String类型的数据
	private final static PSSetTimeStamp psSetTimeStamp = new PSSetTimeStamp();//专门使用PrepareStatement设置TimeStamp类型的数据
	
	private Stack<InternalVariables> db_OperationStack;//为了进行子操作存储的数据，至少存储一个自己元素。
	
	private class InternalVariables{
		String tableName;//用于存储表的名字

		StringBuilder sql;//在最终操作数据库时用，比如executeInsert等
		
		ArrayList<PSSet> psSets;//用于存储？中的值应该执行什么样的操作在PrepareStatement中
		ArrayList<Object> values;//用于存储？中的值，下标与psSets一一对应
		
		Insert insert;//专门拼凑insert操作的sql语句并处理psSets和values
		Delete delete;//专门拼凑delete操作的sql语句
		Update update;//专门拼凑update操作的sql语句并处理psSets和values
		Select select;//专门拼凑select操作的sql语句
		Where where;////专门拼凑where、and(&&)、or(||)操作的sql语句并处理psSets和values
		Exists exists;
		NotExists notExists;
		
		LinkedHashSet<OperationSql> operationSqlLHS;//为了记录是先使用insert还是delete还是update还是select还是where类型的操作，每种操作在一次总操作中只能出现一次。
		public InternalVariables(String tableName) {
			InternalVariables.this.tableName = tableName;
			
			InternalVariables.this.sql = new StringBuilder();
			
			InternalVariables.this.insert = new Insert(tableName, psSets, values);
			InternalVariables.this.delete = new Delete(tableName);
			InternalVariables.this.update = new Update(tableName, psSets, values);
			InternalVariables.this.select = new Select(tableName);
			InternalVariables.this.where = new Where(psSets, values);
			InternalVariables.this.exists = new Exists(tableName);
			InternalVariables.this.notExists = new NotExists(tableName);
			
			InternalVariables.this.operationSqlLHS = new LinkedHashSet<OperationSql>();
		}
		public void setPSSetAndValues(ArrayList<PSSet> psSets, ArrayList<Object> values) {
			InternalVariables.this.psSets = psSets;
			InternalVariables.this.values = values;
			
			insert.setPSSetAndValues(psSets, values);
			update.setPSSetAndValues(psSets, values);
			where.setPSSetAndValues(psSets, values);
		}
		/**
		 * 插入各种数据类型的公共操作。
		 * @param field 字段名
		 * @param value 字段的值
		 */
		private void insertCommon(String field, PSSet psSet, Object value) {
			InternalVariables.this.operationSqlLHS.add(insert);
			InternalVariables.this.insert.insert(field, psSet, value);
		}
		/**
		 * 更新各种数据类型的公共操作。
		 * @param field
		 * @param value
		 */
		private void updateCommon(String field, PSSet psSet, Object value) {
			InternalVariables.this.operationSqlLHS.add(InternalVariables.this.update);
			InternalVariables.this.update.update(field, psSet, value);
		}
		/**
		 * 设置条件语句的公共操作
		 * @param field
		 * @param value
		 */
		private void whereCommon(String field, String operation, PSSet psSet, Object value) {
			InternalVariables.this.operationSqlLHS.add(InternalVariables.this.where);
			InternalVariables.this.where.where(field, operation, psSet, value);
		}
		/**
		 * 设置条件中且的操作的公共部分
		 * @param field
		 * @param value
		 */
		private void andCommon(String field, String operation, PSSet psSet, Object value) {
			InternalVariables.this.where.and(field, operation, psSet, value);
		}
		/**
		 * 设置条件中或的操作的公共部分
		 * @param field
		 * @param value
		 */
		private void orCommon(String field, String operation, PSSet psSet, Object value) {
			InternalVariables.this.where.or(field, operation, psSet, value);
		}
	}
	
	public DB_Operation(Connection connection, String tableName) {
		this.connection = connection;
		
		db_OperationStack = new Stack<>();
		
		InternalVariables iv = new InternalVariables(tableName);
		
		psSets = new ArrayList<PSSet>();
		values = new ArrayList<Object>();
		iv.setPSSetAndValues(psSets, values);
		
		db_OperationStack.push(iv);
	}
	
	/**
	 * 设置是否自动提交。
	 * @param autoCommit
	 * @throws SQLException
	 */
	public void setAutoCommit(Boolean autoCommit) throws SQLException {
		connection.setAutoCommit(autoCommit);
	}
	/**
	 * 手动调用提交。
	 * @throws SQLException
	 */
	public void commit() throws SQLException {
		connection.commit();
	}
	/**
	 * 准备子操作。
	 */
	public DB_Operation prepareSubOperation() {
		InternalVariables iv = new InternalVariables(db_OperationStack.peek().tableName);
		iv.setPSSetAndValues(psSets, values);
		db_OperationStack.push(iv);
		return this;
	}
	public void prepareSubOperation(String tableName) {
		InternalVariables iv = new InternalVariables(tableName);
		iv.setPSSetAndValues(psSets, values);
		db_OperationStack.push(iv);
	}
	/**
	 * 重新设置数据库链接。
	 * @param connection
	 */
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	/**
	 * 重新设置表的名字。
	 * @param tableName
	 * @return
	 */
	public DB_Operation setTableName(String tableName) {
		db_OperationStack.peek().tableName = tableName;
		db_OperationStack.peek().insert.setTableName(db_OperationStack.peek().tableName);
		db_OperationStack.peek().update.setTableName(db_OperationStack.peek().tableName);
		return this;
	}
	/**
	 * insert
	 */
	/**
	 * 插入一个int数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation insert(String field, int value) {
		db_OperationStack.peek().insertCommon(field, psSetInt, value);
		return this;
	}


	/**
	 * 插入一个long数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation insert(String field, long value) {
		db_OperationStack.peek().insertCommon(field, psSetLong, value);
		return this;
	}

	/**
	 * 插入一个String数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation insert(String field, String value) {
		db_OperationStack.peek().insertCommon(field, psSetString, value);
		return this;
	}

	/**
	 * 插入一个Blob类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation insert(String field, Blob value) {
		db_OperationStack.peek().insertCommon(field, psSetBlob, value);
		return this;
	}

	/**
	 * 插入一个Boolean类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation insert(String field, Boolean value) {
		db_OperationStack.peek().insertCommon(field, psSetBoolean, value);
		return this;
	}

	/**
	 * 插入一个Timestamp类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation insert(String field, Timestamp value) {
		db_OperationStack.peek().insertCommon(field, psSetTimeStamp, value);
		return this;
	}
	/**
	 * delete
	 */
	public DB_Operation delete() {
		db_OperationStack.peek().operationSqlLHS.add(db_OperationStack.peek().delete);
		return this;
	}
	/**
	 * set
	 */
	/**
	 * 更新一个int类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation set(String field, int value) {
		db_OperationStack.peek().updateCommon(field, psSetInt, value);
		return this;
	}

	/**
	 * 更新一个long类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation set(String field, long value) {
		db_OperationStack.peek().updateCommon(field, psSetLong, value);
		return this;
	}

	/**
	 * 更新一个String类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation set(String field, String value) {
		db_OperationStack.peek().updateCommon(field, psSetString, value);
		return this;
	}

	/**
	 * 更新一个Blob类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation set(String field, Blob value) {
		db_OperationStack.peek().updateCommon(field, psSetBlob, value);
		return this;
	}

	/**
	 * 更新一个Boolean类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation set(String field, Boolean value) {
		db_OperationStack.peek().updateCommon(field, psSetBoolean, value);
		return this;
	}

	/**
	 * 更新一个Timestamp类型数据
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation set(String field, Timestamp value) {
		db_OperationStack.peek().updateCommon(field, psSetTimeStamp, value);
		return this;
	}

	/**
	 * where
	 */
	/**
	 * 连接查询中的设置条件语句
	 * @param field1
	 * @param field2
	 * @return
	 */
	public DB_Operation joinWhere(String field1, String operation, String field2) {
		db_OperationStack.peek().operationSqlLHS.add(db_OperationStack.peek().where);
		db_OperationStack.peek().where.joinWhere(field1, operation, field2);
		return this;
	}
	
	public void subWhere(String whereSql) {
		db_OperationStack.peek().operationSqlLHS.add(db_OperationStack.peek().where);
		db_OperationStack.peek().where.subWhere(whereSql);
	}
	
	/**
	 * 设置一个int类型的条件
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation where(String field, String operation, int value) {
		db_OperationStack.peek().whereCommon(field, operation, psSetInt, value);
		return this;
	}

	/**
	 * 设置一个long类型的条件
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation where(String field, String operation, long value) {
		db_OperationStack.peek().whereCommon(field, operation, psSetLong, value);
		return this;
	}

	/**
	 * 设置一个String类型的条件
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation where(String field, String operation, String value) {
		db_OperationStack.peek().whereCommon(field, operation, psSetString, value);
		return this;
	}

	/**
	 * and
	 */
	/**
	 * 设置条件中且的操作的连接查询
	 * @param field1
	 * @param field2
	 * @return
	 */
	public DB_Operation joinAnd(String field1, String operation, String field2) {
		db_OperationStack.peek().where.joinAnd(field1, operation, field2);
		return this;
	}
	
	/**
	 * 设置条件中int类型的且的操作
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation and(String field, String operation, int value) {
		db_OperationStack.peek().andCommon(field, operation, psSetInt, value);
		return this;
	}
	
	/**
	 * 设置条件中long类型的且的操作
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation and(String field, String operation, long value) {
		db_OperationStack.peek().andCommon(field, operation, psSetLong, value);
		return this;
	}

	/**
	 * 设置条件中String类型的且的操作
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation and(String field, String operation, String value) {
		db_OperationStack.peek().andCommon(field, operation, psSetString, value);
		return this;
	}

	/**
	 * or
	 */


	/**
	 * 设置条件中或的操作的连接查询
	 * @param field1
	 * @param field2
	 * @return
	 */
	public DB_Operation joinOr(String field1, String operation, String field2) {
		db_OperationStack.peek().where.joinOr(field1, operation, field2);
		return this;
	}
	
	/**
	 * 设置条件中int类型的或的操作
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation or(String field, String operation, int value) {
		db_OperationStack.peek().orCommon(field, operation, psSetInt, value);
		return this;
	}
	
	/**
	 * 设置条件中long类型的或的操作
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation or(String field, String operation, long value) {
		db_OperationStack.peek().orCommon(field, operation, psSetLong, value);
		return this;
	}

	/**
	 * 设置条件中String类型的或的操作
	 * @param field
	 * @param value
	 * @return
	 */
	public DB_Operation or(String field, String operation, String value) {
		db_OperationStack.peek().orCommon(field, operation, psSetString, value);
		return this;
	}
	/**
	 * select
	 */
	/**
	 * 设置要查询的列名
	 * @param field
	 * @return
	 */
	public DB_Operation select(String field) {
		db_OperationStack.peek().operationSqlLHS.add(db_OperationStack.peek().select);
		db_OperationStack.peek().select.select(field);
		return this;
	}

	public DB_Operation selectOnly(String field) {
		db_OperationStack.peek().operationSqlLHS.add(db_OperationStack.peek().select);
		db_OperationStack.peek().select.selectOnly(field);
		return this;
	}
	/**
	 * exists
	 */
	public DB_Operation exists(String existsSql) {
		db_OperationStack.peek().operationSqlLHS.add(db_OperationStack.peek().exists);
		db_OperationStack.peek().exists.exists(existsSql);
		return this;
	}

	/**
	 * notExists
	 */
	public DB_Operation notExists(String notExistsSql) {
		db_OperationStack.peek().operationSqlLHS.add(db_OperationStack.peek().notExists);
		db_OperationStack.peek().notExists.notExists(notExistsSql);
		return this;
	}
	

	/**
	 * execute
	 */
	/**
	 * 所有增删改查的公共操作
	 * @param ps
	 * @throws SQLException
	 */
	private PreparedStatement executeCommon() throws SQLException {
		PreparedStatement ps = connection.prepareStatement(getSql());
		for(int i = 0; i < this.psSets.size(); i++) {
			psSets.get(i).setValue(ps, i + 1, this.values.get(i));
		}
		return ps;
	}

	public Boolean execute() throws SQLException {
		return executeCommon().execute();
	}

	/**
	 * 执行更新操作
	 * @return
	 * @throws SQLException
	 */
	public int executeUpdate() throws SQLException {
		return executeCommon().executeUpdate();
	}

	/**
	 * 执行查询操作
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery() throws SQLException {
		return executeCommon().executeQuery();
	}

	public void prepareNextOperation() {
		db_OperationStack.peek().sql.delete(0, db_OperationStack.peek().sql.length());
		
		psSets.clear();
		values.clear();
		
		db_OperationStack.peek().insert.clearSql();
		db_OperationStack.peek().update.clearSql();
		db_OperationStack.peek().select.clearSql();
		db_OperationStack.peek().where.clearSql();
		db_OperationStack.peek().exists.clearSql();
		db_OperationStack.peek().notExists.clearSql();
		
		db_OperationStack.peek().operationSqlLHS.clear();
	}


	/**
	 * 获得目前构造出的查询的语句
	 * @return
	 */
	public String getSql() {
		InternalVariables iv = db_OperationStack.peek();
		
		Iterator<OperationSql> iterator = iv.operationSqlLHS.iterator();
		while(iterator.hasNext()) {
			iv.sql.append(iterator.next().getSql()).append(" ");
		}
		
		String str = iv.sql.toString();
		
		if(db_OperationStack.size() > 1) {
			db_OperationStack.pop();
		}
//		System.out.println(str);
		return str;
	}
}
